package com.zx.common;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.dbutils.DbUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
 * @Project: BjService
 * @Title: C3P0Util
 * @Description: 数据库动态连接类
 * @author: zhangx
 * @date: 2016年5月12日 下午2:31:57
 * @company: webyun
 * @Copyright: Copyright (c) 2016
 * @version v1.0
 * 
 */
public class C3P0Utils {
	
	private static final Logger logger = LoggerFactory.getLogger(C3P0Utils.class);
	
	private static ComboPooledDataSource cpds = null;
	
	static {
		cpds = new ComboPooledDataSource();
	}

	/**
	 * 获得数据库连接
	 * @return Connection
	 */
	public static Connection getConnection() {
		try {
			return cpds.getConnection();
		} catch (SQLException e) {
			//获得数据库连接的时候出现异常
			//重启获得c3p0数据连接池,如果不行则抛出异常
			try {
				restartDataSource();
				return cpds.getConnection();
			} catch (SQLException e1) {
				logger.error(e.getMessage());
				return null;
			}
		}
	}
	
	/**
	 * 查询表是否存在
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public static boolean isTableExist(String tableName) throws SQLException {
		Connection connection = getConnection();
		Statement createStatement = null;
		ResultSet res = null;
		try {
			createStatement = connection.createStatement();
			res = connection.getMetaData().getTables(null, null, tableName, new String[]{"TABLE"});
			boolean table = res.next();
			return table;
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtils.close(createStatement);
			DbUtils.close(connection);
			DbUtils.close(res);
		}
	}
	
	/**
	 * 删除表
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public static boolean dropTable(String tableName) throws SQLException {
		Connection connection = C3P0Utils.getConnection();
		Statement createStatement = null;
		try {
			createStatement = connection.createStatement();
			createStatement.execute("DROP TABLE " + tableName);//删除;
			return true;
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtils.close(createStatement);
			DbUtils.close(connection);
		}
	}
	
	/**
	 * 创建表
	 * @param createSql
	 * @return
	 * @throws SQLException
	 */
	public static boolean createTable(String createSql) throws SQLException {
		Connection connection = C3P0Utils.getConnection();
		Statement createStatement = null;
		try {
			createStatement = connection.createStatement();
			createStatement.execute(createSql);
			return true;
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtils.close(createStatement);
			DbUtils.close(connection);
		}
	}
	
	/**
	 * 关闭数据源
	 */
	public static void closeDataSource(){
		if(cpds != null){
			cpds.close();
		}
	}
	
	/**
	 * 重启数据源
	 */
	public static void restartDataSource(){
		if(cpds != null){
			cpds.close();
		}
		cpds = new ComboPooledDataSource();
	}
	
	/**
	 * 关闭资源
	 * @param con
	 * @param sta
	 * @param res
	 */
	public static void close(Connection con, Statement sta, ResultSet res) {
		try {
			DbUtils.close(res);
			DbUtils.close(sta);
			DbUtils.close(con);
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
	}
}
